package com.wendyxie.sproutup.db

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import com.wendyxie.sproutup.entity.TreeEntity
import com.wendyxie.sproutup.utils.RandomUtil

class SQLTreeHelper {
    // 定义静态变量
    companion object {
        private const val TABLE_NAME = "tree"
        private const val KEY_TREE_ID = "tree_id"
        private const val KEY_TREE_PRICE = "tree_price"
        private const val KEY_TREE_NAME = "tree_name"
        private const val KEY_TREE_IMAGE_URL = "tree_image_url"

        // 创建表
        fun createTreeTable(db: SQLiteDatabase) {
            val sql = "CREATE TABLE IF NOT EXISTS $TABLE_NAME (" +
                    "$KEY_TREE_ID varchar(36) not null PRIMARY KEY," +
                    "$KEY_TREE_PRICE int," +
                    "$KEY_TREE_NAME varchar(100)," +
                    "$KEY_TREE_IMAGE_URL varchar(100)" +
                    ")"
            db.execSQL(sql)
        }

        // 添加树
        fun addTree(treeEntity: TreeEntity, db: SQLiteDatabase) {
            // 创建 ContentValues 对象，用于存储插入的数据
            val contentValues = ContentValues()
            if (treeEntity.treeId.isNotEmpty()) {
                contentValues.put(KEY_TREE_ID, treeEntity.treeId)
            }else {
                contentValues.put(KEY_TREE_ID, RandomUtil.getUUID())
            }
            contentValues.put(KEY_TREE_PRICE, treeEntity.treePrice)
            contentValues.put(KEY_TREE_NAME, treeEntity.treeName)
            contentValues.put(KEY_TREE_IMAGE_URL, treeEntity.treeImageUrl)

            db.insertOrThrow(TABLE_NAME, null, contentValues)
        }

        // 查询所有树
        fun queryAllTrees(db: SQLiteDatabase): List<TreeEntity> {
            val sql = "SELECT * FROM $TABLE_NAME order by $KEY_TREE_PRICE"
            val cursor = db.rawQuery(sql, null)
            val treeList = mutableListOf<TreeEntity>()
            while (cursor.moveToNext()) {
                treeList.add(cursorToTreeEntity(cursor))
            }
            cursor.close()
            return treeList
        }

        // 根据树ID查询树
        fun queryTreeById(treeId: String, db: SQLiteDatabase): TreeEntity? {
            val sql = "SELECT * FROM $TABLE_NAME WHERE $KEY_TREE_ID = ?"
            val params = arrayOf(treeId)
            val cursor = db.rawQuery(sql, null)
            val treeList: List<TreeEntity> = mutableListOf()
            while (cursor.moveToNext()) {
                cursorToTreeEntity(cursor)
            }
            cursor.close()
            return treeList.getOrNull(0)
        }

        // 将 Cursor 转换为 TreeEntity
        private fun cursorToTreeEntity(cursor: Cursor): TreeEntity {
            return TreeEntity(
                cursor.getString(cursor.getColumnIndex(KEY_TREE_ID)),
                cursor.getInt(cursor.getColumnIndex(KEY_TREE_PRICE)),
                cursor.getString(cursor.getColumnIndex(KEY_TREE_NAME)),
                cursor.getString(cursor.getColumnIndex(KEY_TREE_IMAGE_URL))
            )
        }
    }
}
